Attribute VB_Name = "CDB_TF_5_1"
Sub Export_QE_CDB_TF_5_1_DIA()


'Q-EXACTIVE TRACEFINDER CDB EXPORTER
'This macro exports compound names and scan events from an existing method summary Excel sheet and saves results in
'a .csv file for import into a TraceFinder Compound Database.


'BOOLEAN

Dim Fragments As Boolean
Dim Confirmings As Boolean
Dim PRM As Boolean


'VARIABLES

Dim End_time
Dim RT
Dim Start_time
Dim Window


'INTEGERS

Dim Fragment As Integer
Dim FragmentCount As Integer
Dim First_DataRow As Integer
Dim DataRow As Integer
Dim ExportRow As Integer
Dim EndRow As Integer
Dim FirstConfirming As Integer

Dim MSThresholdArea As Double
Dim MSThresholdHeight As Double
Dim MS2ThresholdArea As Double
Dim MS2ThresholdHeight As Double


'REFERENCES

Dim DataBook As Workbook
Dim DataSheet As Worksheet
Dim DataTable As ListObject
Dim ExportBook As Workbook
Dim ExportSheet As Worksheet
Dim NameCell As Range


'STRINGS

Dim Adduct As String
Dim File_name As String
Dim Fragment_Name As String


Application.ScreenUpdating = False


'SET REFERENCES

Set DataBook = ThisWorkbook
Set DataSheet = ThisWorkbook.Worksheets("Q-Orbitrap Screen")
Set DataTable = DataSheet.ListObjects("Table4")

Fragments = DataSheet.Range("AZ1")
Confirmings = DataSheet.Range("AZ2")
PRM = DataSheet.Range("AZ3")


'REMIND USER WHAT THIS MACRO DOES

MsgBox "A .csv file that can be imported into a TraceFinder 4.1 or 5.1" & vbLf & "Compound Database will be created!", vbInformation, "TF 5.1 CDB Export"


'GET SAVE PATH FROM USER

ChDrive "C"
On Error Resume Next
ChDir "C:\Xcalibur\Methods\"
On Error GoTo 0

File_name = Application.GetSaveAsFilename("TF_5_1_CDB_" & Format(Now(), "mmddyyhhmm") & ".csv", "Comma Separated Files (*.csv),*.csv", 0, "Save TF 5.1 CDB .csv File")
If File_name = "False" Then Exit Sub  'EXIT MACRO WITHOUT SAVING IF USER SELECTS CANCEL



'CREATE A NEW WORKBOOK AND SET StdCheckSheet REFERENCE

Application.DisplayAlerts = False
Set ExportBook = Workbooks.Add
    With ExportBook
        .Worksheets(1).Name = "TraceFinder_CDB_" & Format(Now(), "mmddyyhhmm")
        .SaveAs Filename:=File_name, FileFormat:=xlCSV
    End With
Application.DisplayAlerts = True


Set ExportSheet = ExportBook.Worksheets(1)


'ENTER HEADERS

ExportSheet.Cells(1, 1) = "TraceFinder Compound Database Mass List Export"
ExportSheet.Cells(3, 1) = "Schema Version"
ExportSheet.Cells(4, 1) = "1"

ExportSheet.Cells(3, 2) = "Peak Header Line Number"
ExportSheet.Cells(4, 2) = "6"

ExportSheet.Cells(3, 3) = "Peak Last Row Line Number"
ExportSheet.Cells(3, 4) = "Compound Header Line Number"


'VALUES ENTERED AFTER MASS DATA EXPORT COMPLETE

ExportSheet.Cells(6, 1) = "Compound Name"
ExportSheet.Cells(6, 2) = "Workflow"
ExportSheet.Cells(6, 3) = "Associated Target Peak"
ExportSheet.Cells(6, 4) = "MS Order"
ExportSheet.Cells(6, 5) = "Precursor m/z"
ExportSheet.Cells(6, 6) = "Product m/z"
ExportSheet.Cells(6, 7) = "m/z"
ExportSheet.Cells(6, 8) = "Height Threshold"
ExportSheet.Cells(6, 9) = "Area Threshold"
ExportSheet.Cells(6, 10) = "Collision Energy"
ExportSheet.Cells(6, 11) = "Modification"
ExportSheet.Cells(6, 12) = "Lens"
ExportSheet.Cells(6, 13) = "Energy Ramp"
ExportSheet.Cells(6, 14) = "Ion Coelution"
ExportSheet.Cells(6, 15) = "Ratio Window"
ExportSheet.Cells(6, 16) = "Target Ratio"
ExportSheet.Cells(6, 17) = "Window Type"
ExportSheet.Cells(6, 18) = "PeakPolarity"
ExportSheet.Cells(6, 19) = "Adduct"
ExportSheet.Cells(6, 20) = "Charge State"
ExportSheet.Cells(6, 21) = "Retention Time"
ExportSheet.Cells(6, 22) = "Retention Time Window"
ExportSheet.Cells(6, 23) = "Integration Stragegy"




'DETERMINE NUMBER OF FRAGMENT COLUMNS IN TABLE


For Each headerRange In DataTable.Range.Rows(1).Cells
    If headerRange.Value Like "Fragment*" Then FragmentCount = FragmentCount + 1
Next



'COPY COMPOUND DATA FROM DATA WORKSHEET TO METHOD EXPORT SHEET

ExportRow = 7


'SKIP IF ROW NOT VISIBLE

For Each NameCell In DataTable.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)
DataRow = NameCell.Row - 6


'PROCESS ADDUCT

Adduct = DataTable.ListColumns("QE Adduct").Range(DataRow)
If Left(Adduct, 1) = "[" Then
Adduct = Right(Adduct, Len(Adduct) - 1)
Adduct = Left(Adduct, Len(Adduct) - 2)
End If


'SET THRESHOLDS - EXCEL TABLE USES DATA VALIDATION TO INSURE NUMERIC, POSITIVE, NON-BLANK

MSThresholdArea = DataTable.ListColumns("MS Threshold (Area)").Range(DataRow)
MSThresholdHeight = DataTable.ListColumns("MS Threshold (Height)").Range(DataRow)

MS2ThresholdArea = DataTable.ListColumns("MS2 Threshold (Area)").Range(DataRow)
MS2ThresholdHeight = DataTable.ListColumns("MS2 Threshold (Height)").Range(DataRow)


'COPY COMPOUND DETAILS FROM SUMMARY TABLE

'TARGET PEAK
ExportSheet.Cells(ExportRow, 1) = DataTable.ListColumns("Compound").Range(DataRow)              'COMPOUND NAME
ExportSheet.Cells(ExportRow, 2) = "TargetPeak"                                                  'WORKFLOW
ExportSheet.Cells(ExportRow, 3) = ""                                                            'ASSOCIATED PEAK
If PRM Then
ExportSheet.Cells(ExportRow, 4) = "ms2"                                                         'MS ORDER
ExportSheet.Cells(ExportRow, 5) = DataTable.ListColumns("Adduct m/z").Range(DataRow)            'PRECURSOR M/Z
ExportSheet.Cells(ExportRow, 6) = DataTable.ListColumns("Fragment 1").Range(DataRow)            'PRODUCT M/Z
ExportSheet.Cells(ExportRow, 7) = DataTable.ListColumns("Fragment 1").Range(DataRow)            'M/Z
Else
ExportSheet.Cells(ExportRow, 4) = "ms1"                                                         'MS ORDER
ExportSheet.Cells(ExportRow, 5) = DataTable.ListColumns("Adduct m/z").Range(DataRow)            'PRECURSOR M/Z
ExportSheet.Cells(ExportRow, 6) = DataTable.ListColumns("Adduct m/z").Range(DataRow)            'PRODUCT M/Z
ExportSheet.Cells(ExportRow, 7) = DataTable.ListColumns("Adduct m/z").Range(DataRow)            'M/Z
ExportSheet.Cells(ExportRow, 8) = MSThresholdHeight                                             'HEIGHT THRESHOLD
ExportSheet.Cells(ExportRow, 9) = MSThresholdArea                                               'AREA THRESHOLD
ExportSheet.Cells(ExportRow, 10) = DataTable.ListColumns("NCE").Range(DataRow)                  'COLLISION ENERGY
ExportSheet.Cells(ExportRow, 11) = ""                                                           'MODIFICATION
ExportSheet.Cells(ExportRow, 12) = ""                                                           'LENS
ExportSheet.Cells(ExportRow, 13) = ""                                                           'ENERGY RAMP
ExportSheet.Cells(ExportRow, 14) = ""                                                           'ION COELUTION
ExportSheet.Cells(ExportRow, 15) = ""                                                           'RATIO WINDOW
ExportSheet.Cells(ExportRow, 16) = ""                                                           'TARGET RATIO
ExportSheet.Cells(ExportRow, 17) = ""                                                           'WINDOW TYPE
ExportSheet.Cells(ExportRow, 18) = "Positive"                                                   'PEAK POLARITY
ExportSheet.Cells(ExportRow, 19) = Adduct                                                       'ADDUCT
ExportSheet.Cells(ExportRow, 20) = "1"                                                          'CHARGE STATE
ExportSheet.Cells(ExportRow, 21) = DataTable.ListColumns("RT").Range(DataRow)                   'RT
ExportSheet.Cells(ExportRow, 22) = DataTable.ListColumns("RT Window").Range(DataRow) * 60       'RT WINDOW
ExportSheet.Cells(ExportRow, 23) = "Individual"                                                 'INTEGRATION STRATEGY
End If 'END IF FOR PRM MODE
ExportRow = ExportRow + 1


'CONFIRMINGS
If PRM Then FirstConfirming = 2 Else FirstConfirming = 1

If Confirmings Then
For Fragment = FirstConfirming To FragmentCount
Fragment_Name = "Fragment " & Fragment

'CHECK FOR FRAGMENT - IF BLANK SKIP, ALSO SKIP IF FRAGMENT = PRECURSOR

If DataTable.ListColumns(Fragment_Name).Range(DataRow) <> "" And Round(DataTable.ListColumns(Fragment_Name).Range(DataRow), 2) <> _
Round(DataTable.ListColumns("Adduct m/z").Range(DataRow), 2) Then

ExportSheet.Cells(ExportRow, 1) = DataTable.ListColumns("Compound").Range(DataRow)              'COPY COMPOUND NAME
ExportSheet.Cells(ExportRow, 2) = "Confirming"                                                  'WORKFLOW
ExportSheet.Cells(ExportRow, 3) = "1"                                                           'ASSOCIATED PEAK
ExportSheet.Cells(ExportRow, 4) = "ms2"                                                         'MS ORDER
ExportSheet.Cells(ExportRow, 5) = DataTable.ListColumns("Adduct m/z").Range(DataRow)            'PRECURSOR M/Z
ExportSheet.Cells(ExportRow, 6) = DataTable.ListColumns(Fragment_Name).Range(DataRow)           'PRODUCT M/Z
ExportSheet.Cells(ExportRow, 7) = DataTable.ListColumns(Fragment_Name).Range(DataRow)           'M/Z
ExportSheet.Cells(ExportRow, 8) = MS2ThresholdHeight                                            'HEIGHT THRESHOLD
ExportSheet.Cells(ExportRow, 9) = MS2ThresholdArea                                              'AREA THRESHOLD
ExportSheet.Cells(ExportRow, 10) = DataTable.ListColumns("NCE").Range(DataRow)                  'COLLISION ENERGY
ExportSheet.Cells(ExportRow, 11) = ""                                                           'MODIFICATION
ExportSheet.Cells(ExportRow, 12) = ""                                                           'LENS
ExportSheet.Cells(ExportRow, 13) = ""                                                           'ENERGY RAMP
ExportSheet.Cells(ExportRow, 14) = ""                                                           'ION COELUTION
ExportSheet.Cells(ExportRow, 15) = ""                                                           'RATIO WINDOW
ExportSheet.Cells(ExportRow, 16) = ""                                                           'TARGET RATIO
ExportSheet.Cells(ExportRow, 17) = ""                                                           'WINDOW TYPE
ExportSheet.Cells(ExportRow, 18) = "Positive"                                                   'PEAK POLARITY
ExportSheet.Cells(ExportRow, 19) = ""                                                           'ADDUCT
ExportSheet.Cells(ExportRow, 20) = "1"                                                          'CHARGE STATE
ExportSheet.Cells(ExportRow, 21) = DataTable.ListColumns("RT").Range(DataRow)                   'RT
ExportSheet.Cells(ExportRow, 22) = DataTable.ListColumns("RT Window").Range(DataRow) * 60       'RT WINDOW
ExportSheet.Cells(ExportRow, 23) = "Individual"                                                 'INTEGRATION STRATEGY

ExportRow = ExportRow + 1


End If  'END IF FOR FRAGMENT CHECK

Next Fragment

End If



'FRAGMENTS

If Fragments Then

'LOOP THROUGH FRAGMENT MASSES

For Fragment = 1 To FragmentCount
Fragment_Name = "Fragment " & Fragment

'CHECK FOR FRAGMENT MASS - IF BLANK SKIP

If DataTable.ListColumns(Fragment_Name).Range(DataRow) <> "" Then

ExportSheet.Cells(ExportRow, 1) = DataTable.ListColumns("Compound").Range(DataRow)              'COPY COMPOUND NAME
ExportSheet.Cells(ExportRow, 2) = "Fragment"                                                    'WORKFLOW
ExportSheet.Cells(ExportRow, 3) = "1"                                                           'ASSOCIATED PEAK
ExportSheet.Cells(ExportRow, 4) = "ms2"                                                         'MS ORDER
ExportSheet.Cells(ExportRow, 5) = DataTable.ListColumns("Adduct m/z").Range(DataRow)            'PRECURSOR M/Z
ExportSheet.Cells(ExportRow, 6) = DataTable.ListColumns(Fragment_Name).Range(DataRow)           'PRODUCT M/Z
ExportSheet.Cells(ExportRow, 7) = DataTable.ListColumns(Fragment_Name).Range(DataRow)           'M/Z
ExportSheet.Cells(ExportRow, 8) = MS2ThresholdHeight                                            'HEIGHT THRESHOLD
ExportSheet.Cells(ExportRow, 9) = MS2ThresholdArea                                              'AREA THRESHOLD
ExportSheet.Cells(ExportRow, 10) = DataTable.ListColumns("NCE").Range(DataRow)                  'COLLISION ENERGY
ExportSheet.Cells(ExportRow, 11) = ""                                                           'MODIFICATION
ExportSheet.Cells(ExportRow, 12) = ""                                                           'LENS
ExportSheet.Cells(ExportRow, 13) = ""                                                           'ENERGY RAMP
ExportSheet.Cells(ExportRow, 14) = ""                                                           'ION COELUTION
ExportSheet.Cells(ExportRow, 15) = ""                                                           'RATIO WINDOW
ExportSheet.Cells(ExportRow, 16) = ""                                                           'TARGET RATIO
ExportSheet.Cells(ExportRow, 17) = ""                                                           'WINDOW TYPE
ExportSheet.Cells(ExportRow, 18) = "Positive"                                                   'PEAK POLARITY
ExportSheet.Cells(ExportRow, 19) = ""                                                           'ADDUCT
ExportSheet.Cells(ExportRow, 20) = "1"                                                          'CHARGE STATE
ExportSheet.Cells(ExportRow, 21) = DataTable.ListColumns("RT").Range(DataRow)                   'RT
ExportSheet.Cells(ExportRow, 22) = DataTable.ListColumns("RT Window").Range(DataRow) * 60       'RT WINDOW
ExportSheet.Cells(ExportRow, 23) = "Individual"                                                 'INTEGRATION STRATEGY

ExportRow = ExportRow + 1

End If  'END IF FOR FRAGMENT CHECK

Next Fragment

End If

DataRow = DataRow + 1
Next NameCell


'SET PEAK LAST ROW LINE NUMBER

ExportSheet.Cells(4, 3) = ExportRow - 1

ExportRow = ExportRow + 1


'SET COMPOUND HEADER LINE NUMBER

ExportSheet.Cells(4, 4) = ExportRow


'ENTER COMPOUND HEADERS

ExportSheet.Cells(ExportRow, 1) = "Compound Name"
ExportSheet.Cells(ExportRow, 2) = "Compound Formula"
ExportSheet.Cells(ExportRow, 3) = "Cas Number"
ExportSheet.Cells(ExportRow, 4) = "Category"
ExportSheet.Cells(ExportRow, 5) = "Compound Type"
ExportSheet.Cells(ExportRow, 6) = "Internal Standard Concentration"
ExportSheet.Cells(ExportRow, 7) = "ISTD Protein Name"
ExportSheet.Cells(ExportRow, 8) = "ISTD Compound Name"
ExportSheet.Cells(ExportRow, 9) = "Ionization Field"
ExportSheet.Cells(ExportRow, 10) = "Compound Group"

ExportRow = ExportRow + 1


'ENTER COMPOUND DATA

On Error Resume Next
For Each NameCell In DataTable.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)
DataRow = NameCell.Row - 6


ExportSheet.Cells(ExportRow, 1) = DataTable.ListColumns("Compound").Range(DataRow)
ExportSheet.Cells(ExportRow, 2) = Trim(DataTable.ListColumns("Formula").Range(DataRow))
ExportSheet.Cells(ExportRow, 3) = Trim(DataTable.ListColumns("CAS").Range(DataRow))
ExportSheet.Cells(ExportRow, 4) = DataTable.ListColumns("Standard Mix").Range(DataRow)
ExportSheet.Cells(ExportRow, 5) = "eTargetCompound"
ExportSheet.Cells(ExportRow, 6) = ""
ExportSheet.Cells(ExportRow, 7) = ""
ExportSheet.Cells(ExportRow, 8) = ""
ExportSheet.Cells(ExportRow, 9) = "ESI"
ExportSheet.Cells(ExportRow, 10) = DataTable.ListColumns("Compound Groups").Range(DataRow)

ExportRow = ExportRow + 1
Next NameCell
On Error GoTo 0


'SAVE AND CLOSE .CSV FILE

ExportBook.Save
ExportBook.Close Savechanges = False

MsgBox "CDB export file saved as " & File_name, vbInformation




End Sub







